package com.roc.web.action.agent;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class AgentAffiliationSearchCondition extends BaseSearchCondition{
     /**
	  * @Fields serialVersionUID : TODO
	  */
	    
	private static final long serialVersionUID = 1L;
	//代理商账号
	private String account;
	// 交易起始日期
	private String minDate;
	private String maxDate;
	
	private String opId;//当前操作员id
	private String currrentRoleType;//当前操作员角色类型

	private String orgCode;
	private String oneOrgCode;// 一级机构管理员
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		// TODO Auto-generated method stub
		
	  //代理商账号
		if (Constance.isNotEmpty(account)) {
			filedBeanList.add(new WhereHQLFieldBean("op.account_no", account.trim(), "=", false));
		}
		
	  //开通时间
		if(Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) 
		{
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");
			filedBeanList.add(new WhereHQLFieldBean("TO_CHAR(op.DT_CREATE, 'yyyyMMdd')", startDate, ">=", false));
			filedBeanList.add(new WhereHQLFieldBean("TO_CHAR(op.DT_CREATE, 'yyyyMMdd')", endDate, "<=", false));
		}
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		// TODO Auto-generated method stub
		StringBuffer sb = new StringBuffer();
		sb.append( " select count(*) " );
		sb.append( "   FROM (SELECT T.*, " );
		sb.append( "                (select A1.Name " );
		sb.append( "                   from tbl_area A1 " );
		sb.append( "                  where A1.Ins_Code = T.org_code " );
		sb.append( "                    and A1.id = T.companyid) COMPANYNAME, " );
		sb.append( "                 " );
		sb.append( "                (select T1.Real_Name " );
		sb.append( "                   from T_OPERATORS T1 " );
		sb.append( "                  where T1.ROLE_TYPE in ('02') " );
		sb.append( "                    and T1.oneagentid = T.oneagentid) ONEAGENGNAME " );
		sb.append( "           FROM T_OPERATORS T " );

		// 查询账号角色是 系统管理员 
		if ( currrentRoleType.equals(Role.SYSTEM.getCode()) ) {
			sb.append( " WHERE T.org_code = " + orgCode  );
		}

		// 查询账号角色是 机构管理员 
		if ( currrentRoleType.equals(Role.O_MANAGER.getCode()) ) {
			sb.append( " WHERE T.ONEAGENTID = " + oneOrgCode );
		}

		// 查询账号角色是 代理商 
		if ( currrentRoleType.equals(Role.AGENT.getCode()) ) {
			sb.append( "          START WITH t.ID = " ).append(opId);;
			sb.append( "         CONNECT BY PRIOR t.id = t.supri_id " );
		}
		
		sb.append( "         ) OP " );
		sb.append( "  WHERE op.role_type in  ( '02' , '03' ) " );
		
	/*	
		if(currrentRoleType.trim().equals("03")){//代理商角色
		    sb.append("  select count(*)  ");
		    sb.append("  FROM  (SELECT t.*,(SELECT TT.REAL_NAME FROM T_OPERATORS TT  WHERE TT.ID = TO_NUMBER(T.COMPANYID)) COMPANYNAME,  ");
			sb.append("  (SELECT TT.REAL_NAME FROM T_OPERATORS TT WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME   FROM T_OPERATORS T  ");
			sb.append("  START WITH t.ID =").append(opId);
			sb.append("  CONNECT BY PRIOR  t.id=t.supri_id ) OP  "  );
			sb.append("  WHERE  op.role_type='03' ");
		}else{
		    sb.append("  select count(*)  FROM  ");
	        sb.append("  (SELECT T.*,(SELECT TT.REAL_NAME FROM T_OPERATORS TT  WHERE TT.ID = TO_NUMBER(T.companyid)) COMPANYNAME , ");
		    sb.append("  (SELECT TT.REAL_NAME FROM T_OPERATORS TT WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME   FROM T_OPERATORS T) OP   ");
		    sb.append("  WHERE    op.role_type='03' " );
		}*/
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		
		sb.append( " select op.account_no, " ); 
		sb.append( "        op.mobile , " );
		sb.append( "        OP.ONEAGENGNAME , " );
		sb.append( "        op.REAL_NAME, " );
		sb.append( "        op.CARD_NO, " ); 
		sb.append( "        OP.COMPANYNAME  " );
		sb.append( "   FROM (SELECT T.*, " );
		sb.append( "                (select A1.Name " );
		sb.append( "                   from tbl_area A1 " );
		sb.append( "                  where A1.Ins_Code = T.org_code " );
		sb.append( "                    and A1.id = T.companyid) COMPANYNAME, " );
		sb.append( "                 " );
		sb.append( "                (select T1.Real_Name " );
		sb.append( "                   from T_OPERATORS T1 " );
		sb.append( "                  where T1.ROLE_TYPE in ('02') " );
		sb.append( "                    and T1.oneagentid = T.oneagentid) ONEAGENGNAME " );
		sb.append( "           FROM T_OPERATORS T " );

		// 查询账号角色是 系统管理员 
		if ( currrentRoleType.equals(Role.SYSTEM.getCode()) ) {
			sb.append( " WHERE T.org_code = " + orgCode  );
		}

		// 查询账号角色是 机构管理员 
		if ( currrentRoleType.equals(Role.O_MANAGER.getCode()) ) {
			sb.append( " WHERE T.ONEAGENTID = " + oneOrgCode );
		}

		// 查询账号角色是 代理商 
		if ( currrentRoleType.equals(Role.AGENT.getCode()) ) {
			sb.append( "          START WITH t.ID = " ).append(opId);;
			sb.append( "         CONNECT BY PRIOR t.id = t.supri_id " );
		}
		
		sb.append( "         ) OP " );
		sb.append( "  WHERE op.role_type in  ( '02' , '03' ) " );
		
		
		/*
		if(currrentRoleType.trim().equals("03")){//代理商角色
			sb.append("  SELECT op.account_no, op.mobile ,OP.ONEAGENGNAME ,op.REAL_NAME,op.CARD_NO, OP.COMPANYNAME  ");
			sb.append("  FROM  (SELECT t.*,(SELECT TT.REAL_NAME FROM T_OPERATORS TT  WHERE TT.ID = TO_NUMBER(T.COMPANYID)) COMPANYNAME,  ");
			sb.append("  (SELECT TT.REAL_NAME FROM T_OPERATORS TT WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME   FROM T_OPERATORS T  ");
			sb.append("  START WITH t.ID =").append(opId);
			sb.append("  CONNECT BY PRIOR  t.id=t.supri_id ) OP  " );
			sb.append("  WHERE  op.role_type='03' ");
		}else{//系统管理员
			sb.append("  SELECT op.account_no,op.mobile ,OP.ONEAGENGNAME ,op.REAL_NAME,op.CARD_NO, OP.COMPANYNAME  ");
		    sb.append("  FROM  (SELECT T.*,(SELECT TT.REAL_NAME FROM T_OPERATORS TT  WHERE TT.ID = TO_NUMBER(T.companyid)) COMPANYNAME ,   ");
			sb.append("  (SELECT TT.REAL_NAME FROM T_OPERATORS TT WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME   FROM T_OPERATORS T) OP  ");
			sb.append("  WHERE  op.role_type='03' ");
		}*/
		
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return "  ";
		/*return "order by op.account_no desc ";*/
	}

	public String getAccount() {
		return account;
	}

	public void setAccount(String account) {
		this.account = account;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getCurrrentRoleType() {
		return currrentRoleType;
	}

	public void setCurrrentRoleType(String currrentRoleType) {
		this.currrentRoleType = currrentRoleType;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getOneOrgCode() {
		return oneOrgCode;
	}

	public void setOneOrgCode(String oneOrgCode) {
		this.oneOrgCode = oneOrgCode;
	}


	
	

}
